

clear all

adopath + "H:\Zheng_10223\stata\tabout"
adopath + "H:\Zheng_10223\stata\tab2xl"


* CENSUS 1991 	

	cd "G:\Census_1991\Data\Stata\130425_1991_Census"
	
	
	use  age citizen occ* sex birth_pl hhinc efinc  soc91 id persno empin totinc wages cma pr comp*  childcount cfbabes totyrs marsth dgreer  using "1991_Census.dta", clear

	* code either married at some point or never marreid
	
	gen married=0
	replace married=1 if marsth==2 | marsth==3 | marsth==5 
	
	drop marsth 
	
	* this keeps all citizens born in Canada
	keep if citizen==1  |citizen==5 | citizen==6
	drop citizen
	
	* year of census
	gen year=1991
	
	gen person_weight=compw5
	
	* rename child count
	rename childcount numkids
	
	cd "H:\Zheng_10223\Joint\Census"


save "census1991_canadians.dta", replace 
****************************************************************************

* Census 1996 
	cd "G:\Census_1996\rdc\Census_1996_v2\data\data\stata"
	
	use  pob* age  citizen  sex soc91 id hhinc totyrs   efinc  persno empin totinc wages cma pr comp* kid  marsth  dgreer using "CEN_1996_F1_v2.dta" , clear

	rename pob birt_pl
	
	gen married=0
	replace married=1 if marsth==2 | marsth==3 | marsth==5 
	
		
		* this keeps all citizens born in Canada (option 5 is canada birth + 1 other, option 6 is 2 others)
	keep if citizen==2 |citizen==5 |citizen==6
	drop citizen
	
	gen year=1996

gen person_weight=compw2
	 drop marsth 

rename kid numkids 


	
	cd "H:\Zheng_10223\Joint\Census"

save "census1996_canadians.dta", replace 

************ 2001 census  
cd "G:\Census_2001\rdc-cdr\CEN_REC_2001_v2\data_donnees\data\stata_en"

use age citbir kid sex pob  soc91h nocs2001 hhinc naics* totyrs  efinc  id persno empin totinc wages cma pr comp* marsth dgreer using "Census_2001_Master_F1_v1.dta", clear
	
	
	* code either married at some point or never marreid
	
	gen married=0
	replace married=1 if marsth==2 | marsth==3 | marsth==5 
	
	 drop marsth 
	
	
	* this keeps all citizens born in Canada
	keep if citbir==1 
	drop citbir
	
	gen year=2001

gen person_weight=compw2
	rename kid numkids
	cd "H:\Zheng_10223\Joint\Census"

save "census2001_canadians.dta", replace 





*************** 2006 **************** 

	cd "G:\Census_2006\rdc-cdr\CEN_REC_2006_v2\data_donnees\data\stata_en"

	use  age citbir sex  soc91h naics* hhinc  nocs* id   efinc_pp  persno empin totinc wages cma pr comp* kid hcdd marsth using "cen_2006_f1_v2.dta", clear

gen person_weight=compw2


	cd "H:\Zheng_10223\Joint\Census"
	* this keeps all citizens born in Canada
	keep if citbir==1 
	drop citbir
	rename kid numkids
	gen year=2006
	rename efinc_pp efinc
gen married=0
	replace married=1 if marsth==2 | marsth==3 | marsth==5 
	gen dgreer=1
*hcdd=2 is high school and dgrer 2 is high school  
replace dgreer=2 if hcdd==2
replace dgreer=3 if hcdd==3 | hcdd==4
replace dgreer=4 if hcdd==5|hcdd==6|hcdd==7
replace dgreer=5 if hcdd==8
replace dgreer=6 if hcdd==9
replace dgreer=7 if hcdd==10
replace dgreer=8 if hcdd==11
replace dgreer=9 if hcdd==12
replace dgreer=10 if hcdd==13
	cd "H:\Zheng_10223\Joint\Census"

save "census2006_canadians.dta", replace 

********* 2011 Census (NHS) ************
cd "G:\NHS_2011\rdc-cdr\NHS_ENM_2011_v2\data_donnees\data\stata_en"

 * checked: january 30, 2025* 
use age citbir naics* noc* sex hhinc cf_id citbir persno  marsth efinc_pp  empin totinc wages cma pr comp*  hcdd cfkidnum using "NHS_2011_F1_v2.dta", clear 

* convert highestdegree earned to dgreer


gen dgreer=1
*hcdd=2 is high school and dgrer 2 is high school
replace dgreer=2 if hcdd==2
replace dgreer=3 if hcdd==3 | hcdd==4
replace dgreer=4 if hcdd==5|hcdd==6|hcdd==7
replace dgreer=5 if hcdd==8
replace dgreer=6 if hcdd==9
replace dgreer=7 if hcdd==10
replace dgreer=8 if hcdd==11
replace dgreer=9 if hcdd==12
replace dgreer=10 if hcdd==13


	
	gen married=0
	replace married=1 if marsth==2 | marsth==3 | marsth==4| marsth==6
	
	 drop marsth 
	

cd "H:\Zheng_10223\Joint\Census"
	* this keeps all citizens born in Canada
	keep if citbir==1 
	drop citbir
	
	gen year=2011
gen person_weight=compw2
		rename efinc_pp efinc

	cd "H:\Zheng_10223\Joint\Census"
	
	rename noc2011 noc2011raw

save "census2011_canadians.dta", replace 


***************************** 

* append for age earnings profile of natives only
use "census1991_canadians.dta", clear

foreach i of numlist 1996 2001 2006 2011 {
	append using "census`i'_canadians.dta" , force 
}

*save "census_allcanadians.dta", replace 

* adjust with cpi
merge m:1 year using "H:\Zheng_10223\Joint\cpi\cpi.dta"
drop if _merge!=3
drop _merge 
drop cpi cpi100 
**********************
keep totinc empin hhinc wages sex age year cpi2020 person_weight efinc 

* real income
gen totinc_real=totinc/cpi2020
gen empin_real=empin/cpi2020
gen wages_real=wages/cpi2020
gen hhinc_real=hhinc/cpi2020
gen efinc_real=efinc/cpi2020

****** get the age coefficients 
* working age:
*20-65

keep if age>19 & age<66

drop wages cpi2020 wages_real empin totinc hhinc  efinc

******************************************************************************

* census 1991: 

use "H:\Zheng_10223\Joint\Census\census1991_canadians.dta", clear 

* 

* get everything to noc 2011 
gen noc2011=""
replace noc2011="00" if soc91<=6
replace noc2011="01" if soc91>=7 & soc91<=14
replace noc2011="06" if soc91>=15 & soc91<=39
replace noc2011="07" if soc91>=40 & soc91<=43
replace noc2011="11" if soc91>=44 & soc91<=49
replace noc2011="12" if soc91>=55 & soc91<=72
replace noc2011="13" if soc91>=50 & soc91<=54
replace noc2011="14" if soc91>=73 & soc91<=92
replace noc2011="15" if soc91>=93 & soc91<=101
replace noc2011="21" if soc91>=102 & soc91<=128
replace noc2011="22" if soc91>=129 & soc91<=157
replace noc2011="30" if soc91==171 |soc91==172
replace noc2011="31" if soc91>=158 & soc91<=170
replace noc2011="32" if (soc91>=173 & soc91<=186) |  (soc91>=188 & soc91<=189)
replace noc2011="34" if soc91==187|(soc91>=190 & soc91<=192)
replace noc2011="40" if soc91>=208 & soc91<=213
replace noc2011="41" if (soc91>=200 & soc91<=207) |  (soc91>=193 & soc91<=198)
replace noc2011="42" if soc91>=214 & soc91<=219
replace noc2011="43" if (soc91==199 )| (soc91>=273 & soc91<=280)
replace noc2011="44" if soc91>=220 & soc91<=225
replace noc2011="51" if (soc91>=226 & soc91<=233) | (soc91>=243 & soc91<=253)
replace noc2011="52" if soc91>=234 & soc91<=242
replace noc2011="62" if soc91==254
replace noc2011="63" if soc91>=255 & soc91<=259
replace noc2011="64" if soc91>=260 & soc91<=264
replace noc2011="65" if (soc91>=268 & soc91<=272) | (soc91>=281 & soc91<=299)
replace noc2011="66" if (soc91==261 )| (soc91>=265 & soc91<=267)
replace noc2011="67" if soc91>=300 & soc91<=313
replace noc2011="72" if soc91>=314 & soc91<=338
replace noc2011="73" if soc91>=339 & soc91<=354
replace noc2011="74" if soc91>=355 & soc91<=381
replace noc2011="75" if soc91>=382 & soc91<=401
replace noc2011="76" if soc91>=402 & soc91<=405
replace noc2011="82" if soc91>=406 & soc91<=417
replace noc2011="84" if soc91>=418 & soc91<=428
replace noc2011="86" if soc91>=429 & soc91<=434
replace noc2011="92" if soc91>=435 & soc91<=451
replace noc2011="94" if soc91>=452 & soc91<=490
replace noc2011="95" if soc91>=491 & soc91<=505
replace noc2011="96" if soc91>=506 & soc91<=512

* create new value for people not working 
replace noc2011="99" if soc91==513
keep noc2011 totinc dgreer empin married hhinc efinc person_weight age cm sex year totyrs soc91 pr numkids


* age group : 45-49
keep if age>=45 & age<=49

* keep only people with kids
keep if numkids>0

save "H:\Zheng_10223\Joint\Census\census1991_occdata.dta", replace 
*************** 1996 Census 
use "H:\Zheng_10223\Joint\Census\census1996_canadians.dta", clear

* get everything to noc 2011 
gen noc2011=""
replace noc2011="00" if soc91<=6
replace noc2011="01" if soc91>=7 & soc91<=14
replace noc2011="06" if soc91>=15 & soc91<=39
replace noc2011="07" if soc91>=40 & soc91<=43
replace noc2011="11" if soc91>=44 & soc91<=49
replace noc2011="12" if soc91>=55 & soc91<=72
replace noc2011="13" if soc91>=50 & soc91<=54
replace noc2011="14" if soc91>=73 & soc91<=92
replace noc2011="15" if soc91>=93 & soc91<=101
replace noc2011="21" if soc91>=102 & soc91<=128
replace noc2011="22" if soc91>=129 & soc91<=157
replace noc2011="30" if soc91==171 |soc91==172
replace noc2011="31" if soc91>=158 & soc91<=170
replace noc2011="32" if (soc91>=173 & soc91<=186) |  (soc91>=188 & soc91<=189)
replace noc2011="34" if soc91==187|(soc91>=190 & soc91<=192)
replace noc2011="40" if soc91>=208 & soc91<=213
replace noc2011="41" if (soc91>=200 & soc91<=207) |  (soc91>=193 & soc91<=198)
replace noc2011="42" if soc91>=214 & soc91<=219
replace noc2011="43" if (soc91==199 )| (soc91>=273 & soc91<=280)
replace noc2011="44" if soc91>=220 & soc91<=225
replace noc2011="51" if (soc91>=226 & soc91<=233) | (soc91>=243 & soc91<=253)
replace noc2011="52" if soc91>=234 & soc91<=242
replace noc2011="62" if soc91==254
replace noc2011="63" if soc91>=255 & soc91<=259
replace noc2011="64" if soc91>=260 & soc91<=264
replace noc2011="65" if (soc91>=268 & soc91<=272) | (soc91>=281 & soc91<=299)
replace noc2011="66" if (soc91==261 )| (soc91>=265 & soc91<=267)
replace noc2011="67" if soc91>=300 & soc91<=313
replace noc2011="72" if soc91>=314 & soc91<=338
replace noc2011="73" if soc91>=339 & soc91<=354
replace noc2011="74" if soc91>=355 & soc91<=381
replace noc2011="75" if soc91>=382 & soc91<=401
replace noc2011="76" if soc91>=402 & soc91<=405
replace noc2011="82" if soc91>=406 & soc91<=417
replace noc2011="84" if soc91>=418 & soc91<=428
replace noc2011="86" if soc91>=429 & soc91<=434
replace noc2011="92" if soc91>=435 & soc91<=451
replace noc2011="94" if soc91>=452 & soc91<=490
replace noc2011="95" if soc91>=491 & soc91<=505
replace noc2011="96" if soc91>=506 & soc91<=514



* create new value for people not working 
replace noc2011="99" if soc91==515


keep noc2011 totinc dgreer empin efinc hhinc married person_weight age cm sex year totyrs soc91 pr numkids
* age group : 45-49
keep if age>=45 & age<=49

* keep only people with kids
keep if numkids>0


**********************

rename totyrsr totyrs

*decode ma, gen(cmastring)
save "H:\Zheng_10223\Joint\Census\census1996_occdata.dta", replace 

*************** 2001 Census 
use "H:\Zheng_10223\Joint\Census\census2001_canadians.dta", clear

gen noc2011=""
replace noc2011="00" if soc91<=6
replace noc2011="01" if soc91>=7 & soc91<=14
replace noc2011="06" if soc91>=15 & soc91<=39
replace noc2011="07" if soc91>=40 & soc91<=43
replace noc2011="11" if soc91>=44 & soc91<=49
replace noc2011="12" if soc91>=56 & soc91<=72
replace noc2011="13" if soc91>=50 & soc91<=55
replace noc2011="14" if soc91>=73 & soc91<=90
replace noc2011="15" if soc91>=91 & soc91<=99
replace noc2011="21" if soc91>=100 & soc91<=124
replace noc2011="22" if soc91>=129 & soc91<=153
replace noc2011="30" if soc91==167 |soc91==168
replace noc2011="31" if soc91>=154 & soc91<=166
replace noc2011="32" if (soc91>=169 & soc91<=182) |  (soc91>=184 & soc91<=185)
replace noc2011="34" if soc91==183|(soc91>=186 & soc91<=188)
replace noc2011="40" if soc91>=204 & soc91<=209
replace noc2011="41" if (soc91>=196 & soc91<=203) |  (soc91>=189 & soc91<=194)
replace noc2011="42" if soc91>=210 & soc91<=215
replace noc2011="43" if (soc91==195 )| (soc91>=269 & soc91<=276)
replace noc2011="44" if soc91>=216 & soc91<=221
replace noc2011="51" if (soc91>=222 & soc91<=229) | (soc91>=239 & soc91<=249)
replace noc2011="52" if soc91>=230 & soc91<=238
replace noc2011="62" if soc91==250
replace noc2011="63" if soc91>=251 & soc91<=255
replace noc2011="64" if soc91>=256 & soc91<=260
replace noc2011="65" if (soc91>=264 & soc91<=268) | (soc91>=281 & soc91<=299)
replace noc2011="66" if (soc91==257 )| (soc91>=261 & soc91<=263)

replace noc2011="67" if soc91>=296 & soc91<=307
replace noc2011="72" if soc91>=308 & soc91<=332
replace noc2011="73" if soc91>=333 & soc91<=348
replace noc2011="74" if soc91>=349 & soc91<=375
replace noc2011="75" if soc91>=376 & soc91<=395
replace noc2011="76" if soc91>=396 & soc91<=399

replace noc2011="82" if soc91>=400 & soc91<=411
replace noc2011="84" if soc91>=412 & soc91<=422
replace noc2011="86" if soc91>=423 & soc91<=428
replace noc2011="92" if soc91>=429 & soc91<=441
replace noc2011="94" if soc91>=442 & soc91<=484
replace noc2011="95" if soc91>=485 & soc91<=499
replace noc2011="96" if soc91>=500 & soc91<=508

* create new value for people not working 
replace noc2011="99" if soc91==509

keep noc2011 totinc empin efinc married hhinc person_weight age cm sex year totyrs soc91 pr numkids dgreer
* age group : 45-49
keep if age>=45 & age<=49

* keep only people with kids
keep if numkids>0

rename totyrsr totyrs
rename soc91h soc91

*decode ma, gen(cmastring)
save "H:\Zheng_10223\Joint\Census\census2001_occdata.dta", replace 


*************** 2006 Census 
use "H:\Zheng_10223\Joint\Census\census2006_canadians.dta", clear

* convert 2006 nocsmaj to 2011 nocs
gen noc2011=""
replace noc2011="00" if nocsmaj==1
replace noc2011="01" if nocsmaj==2
replace noc2011="06" if nocsmaj==3
replace noc2011="07" if nocsmaj==4
replace noc2011="11" if nocsmaj==5

replace noc2011="12" if nocsmaj==6
replace noc2011="14" if nocsmaj==7
replace noc2011="13" if nocsmaj==8
replace noc2011="14" if nocsmaj==9
replace noc2011="15" if nocsmaj==10
replace noc2011="21" if nocsmaj==11
replace noc2011="22" if nocsmaj==12
replace noc2011="31" if nocsmaj==13
replace noc2011="30" if nocsmaj==14
replace noc2011="32" if nocsmaj==15
replace noc2011="34" if nocsmaj==16
replace noc2011="41" if nocsmaj==17
replace noc2011="40" if nocsmaj==18
replace noc2011="42" if nocsmaj==19
replace noc2011="51" if nocsmaj==20
replace noc2011="52" if nocsmaj==21
replace noc2011="62" if nocsmaj==22
replace noc2011="63" if nocsmaj==23
replace noc2011="64" if nocsmaj==24
replace noc2011="66" if nocsmaj==25
replace noc2011="67" if nocsmaj==26
replace noc2011="67" if nocsmaj==27
replace noc2011="43" if nocsmaj==28
replace noc2011="67" if nocsmaj==29
replace noc2011="44" if nocsmaj==30
replace noc2011="67" if nocsmaj==31
replace noc2011="72" if nocsmaj==32
replace noc2011="72" if nocsmaj==33
replace noc2011="73" if nocsmaj==34
replace noc2011="75" if nocsmaj==35
replace noc2011="73" if nocsmaj==36
replace noc2011="74" if nocsmaj==37
replace noc2011="75" if nocsmaj==38
replace noc2011="75" if nocsmaj==39
replace noc2011="76" if nocsmaj==40
replace noc2011="84" if nocsmaj==41
replace noc2011="84" if nocsmaj==42
replace noc2011="86" if nocsmaj==43
replace noc2011="92" if nocsmaj==44
replace noc2011="94" if nocsmaj==45
replace noc2011="95" if nocsmaj==46
replace noc2011="96" if nocsmaj==47
replace noc2011="99" if nocsmaj==48

gen soc91=.

* create total years of schooling variable 
gen totyrs=11
replace totyrs=12 if dgreer==2
replace totyrs=14 if dgreer==3|dgreer==4|dgreer==5
replace totyrs=16 if dgreer==6
replace totyrs=18 if dgreer==7|dgreer==8|dgreer==9|dgreer==10


keep noc2011 totinc empin efinc married hhinc person_weight age cm sex year totyrs soc91 pr numkids dgreer





* age group : 45-49
keep if age>=45 & age<=49

* keep only people with kids
keep if numkids>0



save "H:\Zheng_10223\Joint\Census\census2006_occdata.dta", replace 


*************** 2011 Census : already in noc2011
use "H:\Zheng_10223\Joint\Census\census2011_canadians.dta", clear

gen noc2011=noc11maj

replace noc2011=99 if noc11maj==-3|noc11maj==-5

rename cfkidnum numkids

* create total years of schooling variable 
gen totyrs=11
replace totyrs=12 if dgreer==2
replace totyrs=14 if dgreer==3|dgreer==4|dgreer==5
replace totyrs=16 if dgreer==6
replace totyrs=18 if dgreer==7|dgreer==8|dgreer==9|dgreer==10

keep noc2011 totinc empin efinc married hhinc person_weight age cm sex year totyrs pr numkids dgreer
* age group : 45-49
keep if age>=45 & age<=49


* keep only people with kids
keep if numkids>0

gen soc91=.

save "H:\Zheng_10223\Joint\Census\census2011_occdata.dta", replace 

************ convert
use "H:\Zheng_10223\Joint\Census\census1991_occdata.dta", clear
outsheet using "H:\Zheng_10223\Joint\Census\census1991_occdata.csv", replace 

use "H:\Zheng_10223\Joint\Census\census1996_occdata.dta", clear
outsheet using "H:\Zheng_10223\Joint\Census\census1996_occdata.csv", replace 


use "H:\Zheng_10223\Joint\Census\census2001_occdata.dta", clear
outsheet using "H:\Zheng_10223\Joint\Census\census2001_occdata.csv", replace 


use "H:\Zheng_10223\Joint\Census\census2011_occdata.dta", clear
outsheet using "H:\Zheng_10223\Joint\Census\census2011_occdata.csv", replace 
